package Gr

import (
	"fmt"
	"db2s/ETL"
	"db2s/global"
	"db2s/parDef"
	"db2s/ref"
	"math"
	"sort"
	"strconv"
	"strings"
)

func (im IndexMe) Set(parameter string, value any) (err error) {
	return ref.UpdateStructJsonValue(im, parameter, value)
}
func (im IndexMe) Result(object string) global.Return {
	switch object {
	default:
		return global.Return{}
	}
}

var intType = map[string]string{"FLOAT": "int", "DOUBLE": "int", "DECIMAL": "int", "NUMERIC": "int", "TINYINT": "int", "SMALLINT": "int", "MEDIUMINT": "int", "INT": "int", "BIGINT": "int"}
var timeType = map[string]string{"YEAR": "time", "DATE": "time", "TIME": "time", "DATETIME": "time", "TIMESTAMP": "time"}
var stringType = map[string]string{"CHAR": "string", "VARCHAR": "string"}
var typeCost = map[string]int{"int": 1, "time": 2, "string": 3, "text": 4}

type statisticsS struct {
	Schema           string `json:"databaseName"`
	Table            string `json:"tableName"`
	IndexName        string `json:"indexName"`
	UniqueType       string `json:"uniqueType"`
	IndexSeq         string `json:"columnSeq"`
	IndexColumn      string `json:"columnName"`
	PreFix           string `json:"prefix"`
	IndexCardinality string `json:"cardinality"`
}
type subInnodbIndexStats struct {
	Rows            string   `json:"tableRows"`
	ColumnName      []string `json:"columnName"`
	StatName        string   `json:"statName"`
	StatValue       string   `json:"statValue"`
	SampleSize      string   `json:"sampleSize"`
	StatDescription string   `json:"statDescription"`
}
type InnodbIndexStats struct {
	IndexName string                `json:"indexName"`
	SubStats  []subInnodbIndexStats `json:"subStats"`
}
type IndexColumnMe struct {
	ColumnType string `json:"columnType"` //--
	ColumnName string `json:"columnName"` //--
	ColumnSeq  string `json:"columnSeq"`
	IndexSeq   string `json:"indexSeq"`
	SubPart    int64  `json:"subPart"` //前缀索引长度
}
type nameColumnMe struct {
	Schema             string          `json:"databaseName"`
	Table              string          `json:"tableName"` //--
	IndexName          string          `json:"indexName"` //--
	Column             []IndexColumnMe `json:"Column"`
	Null               string          `json:"null"`
	SubPart            string          `json:"subPart"` //前缀索引长度
	AutoIncrement      string          `json:"autoIncrement"`
	NonUnique          string          `json:"nonUnique"`
	Cardinality        int64           `json:"cardinality"`   //--
	ColumnKey          string          `json:"columnKey"`     //--判断是普通索引，还是唯一索引，还是普通索引
	IndexCategory      string          `json:"indexCategory"` //--
	indexMaxLength     int
	indexLengthDetails []int
}

func (im IndexMe) nameColumnBase(parameter parDef.Parameter) (colMeta global.Return, err error) {
	//cm := ColumnMe{Parameter: im.Parameter}
	cm := ColumnMe{}
	if colMeta, err = cm.Meta(parameter); err != nil {
		return
	}
	return
}

func (im IndexMe) statistics(parameter parDef.Parameter) (result global.Return, err error) {
	var execSql []string
	var event = "[statistics]"
	var res []statisticsS
	execSql = append(execSql, fmt.Sprintf("SELECT \n"+
		"table_schema as `databaseName`,\n"+
		"table_name as `tableName`,\n"+
		"NON_UNIQUE as `uniqueType`,\n"+
		"INDEX_NAME as `indexName`,\n"+
		"SEQ_IN_INDEX as `columnSeq`,\n"+
		"COLUMN_NAME as `columnName`,\n"+
		"SUB_PART as `prefix`,\n"+
		"CARDINALITY as `cardinality` \n"+
		"FROM \n"+
		"information_schema.STATISTICS \n"+
		"where \n"+
		"table_schema in ('%v') and table_name in ('%v')\n"+
		"order by INDEX_NAME,SEQ_IN_INDEX", parameter.Object.Schema, parameter.Object.Table))
	if result, err = forExecQuerySql(event, parameter, ETL.SMap, execSql); err != nil || result.Result == nil {
		return
	}
	var p []map[string]any
	for _, v := range result.Result.([]map[string]any) {
		vv := mapNullActive(v)
		var dd = statisticsS{}
		if err = ref.MapToStruct(vv, &dd); err != nil {
			return
		}
		res = append(res, dd)
		p = append(p, vv)
	}
	result.Result, result.InsideResult = p, res
	return
}

func indexNameGroupByColumnName(ss []statisticsS) (result map[string][]string) {
	result = make(map[string][]string)
	for _, v := range ss {
		if _, ok := result[v.IndexName]; !ok {
			result[v.IndexName] = []string{v.IndexColumn}
		} else {
			result[v.IndexName] = append(result[v.IndexName], v.IndexColumn)
		}
	}
	return
}
func indexCardinality(nameResult *nameColumnMe, indexMeta []statisticsS) {
	var car []int64
	for _, vv := range indexMeta {
		if strings.EqualFold(vv.IndexName, nameResult.IndexName) {
			if seq, _ := stringAnyToInt64(vv.IndexSeq); seq > 2 {
				continue
			}
			var r int64
			r, _ = stringAnyToInt64(vv.IndexCardinality)
			car = append(car, r)
		}
	}
	sort.Slice(car, func(i, j int) bool {
		return car[i] > car[j]
	})
	nameResult.Cardinality = car[0]
}
func columnKey(nameResult *nameColumnMe, indexMeta []statisticsS) {
	var UniqueMark int64
	for _, vv := range indexMeta {
		if strings.EqualFold(vv.IndexName, nameResult.IndexName) {
			UniqueMark, _ = stringAnyToInt64(vv.UniqueType)
			break
		}
	}
	switch UniqueMark {
	case 0:
		nameResult.ColumnKey = "U"
		if strings.EqualFold(nameResult.IndexName, "PRIMARY") {
			nameResult.ColumnKey = "P"
		}
		nameResult.NonUnique = "1"
	case 1:
		nameResult.ColumnKey = "I"
		nameResult.NonUnique = "0"
	}
}

// http://mysql.taobao.org/monthly/2022/10/05/
// http://mysql.taobao.org/monthly/2024/01/03/
// http://mysql.taobao.org/monthly/2021/05/03/
// https://www.cnblogs.com/wanbin/p/9554091.html
func indexNameMerge(res []map[string]any) (result map[string][]any) {
	result = make(map[string][]any)
	for _, v := range res {
		var iName = fmt.Sprintf("%v", v["indexName"])
		if _, ok := result[iName]; !ok {
			result[iName] = []any{v}
		} else {
			result[iName] = append(result[iName], v)
		}
	}
	return
}
func indexNameSubOption(ss []any) (result []any) {
	for _, v := range ss {
		vv := mapNullActive(v.(map[string]any))
		statName, ok1 := vv["statName"]
		statDescription, ok2 := vv["statDescription"]
		if ok1 && ok2 && strings.HasPrefix(strings.ToLower(strings.TrimSpace(fmt.Sprintf("%v", statName))), "n_diff_pf") {
			vv["columnName"] = func() (res []any) {
				for _, p := range strings.Split(strings.TrimSpace(fmt.Sprintf("%v", statDescription)), ",") {
					res = append(res, p)
				}
				return
			}()
		}
		result = append(result, vv)
	}
	return
}
func (im IndexMe) ColumnDistinct(parameter parDef.Parameter) (result global.Return, err error) {
	var event = "[columnDistinct]"
	var execSql []string
	var res []InnodbIndexStats

	execSql = append(execSql, fmt.Sprintf("select "+
		"t.n_rows AS `tableRows`,"+
		"i.index_name AS `indexName`,"+
		"i.stat_name AS `statName`,"+
		"i.stat_value AS `statValue`,"+
		"i.sample_size AS `sampleSize`,"+
		"i.stat_description AS `statDescription` "+
		"from "+
		"mysql.innodb_index_stats i "+
		"left join "+
		"mysql.innodb_table_stats t "+
		"on i.database_name=t.database_name and i.table_name=t.table_name "+
		"where "+
		"i.database_name='%v' and i.table_name='%v'", parameter.Object.Schema, parameter.Object.Table))
	if result, err = forExecQuerySql(event, parameter, ETL.SMap, execSql); err != nil || result.Result == nil {
		return
	}
	var pp []map[string]any
	for kk, vv := range indexNameMerge(result.Result.([]map[string]any)) {
		var p = make(map[string]any)
		p["indexName"] = kk
		p["subStats"] = indexNameSubOption(vv)
		pp = append(pp, p)
	}
	for _, v := range pp {
		var sub InnodbIndexStats
		if err = ref.MapToStruct(v, &sub); err != nil {
			return
		}
		res = append(res, sub)
	}
	result.Result, result.InsideResult = pp, res
	return
}

func IndexColumnInit(nameResult *nameColumnMe, ss []statisticsS, cc []columnMeta) {
	var p []IndexColumnMe
	for kk, v := range indexNameGroupByColumnName(ss) {
		if !strings.EqualFold(nameResult.IndexName, kk) {
			continue
		}
		for k, vv := range v {
			for _, vvv := range cc {
				if strings.EqualFold(vv, vvv.ColumnName) {
					p = append(p, IndexColumnMe{
						ColumnName: vvv.ColumnName,
						ColumnType: vvv.ColumnType,
						ColumnSeq:  vvv.ColumnSeq,
						IndexSeq:   strconv.Itoa(k),
					})
				}
			}
		}
	}
	nameResult.Column = p
}
func NameColumnBaseInit(indexMeta []statisticsS) (result []*nameColumnMe) {
	var p = make(map[string]int)
	for _, v := range indexMeta {
		if _, ok := p[v.IndexName]; !ok {
			result = append(result, &nameColumnMe{
				Schema:        v.Schema,
				Table:         v.Table,
				IndexName:     v.IndexName,
				IndexCategory: "local",
			})
		}
	}
	return
}
func (im IndexMe) NameColumn(parameter parDef.Parameter) (result global.Return, err error) {
	var colMeta []columnMeta
	if result, err = im.nameColumnBase(parameter); err != nil || result.Result == nil {
		return
	}
	colMeta = result.InsideResult.([]columnMeta)
	if result, err = im.statistics(parameter); err != nil || result.Result == nil {
		return
	}
	indexMeta := result.InsideResult.([]statisticsS)
	var resMap []map[string]any
	var resStr []nameColumnMe
	for _, vv := range NameColumnBaseInit(indexMeta) {
		indexCardinality(vv, indexMeta)
		columnKey(vv, indexMeta)
		IndexColumnInit(vv, indexMeta, colMeta)
		var Map map[string]any
		if Map, err = ref.StructToMap(*vv); err != nil {
			return
		}
		resMap = append(resMap, Map)
		resStr = append(resStr, *vv)
	}
	result.Result = resMap
	result.InsideResult = resStr
	return
}

// 按照索引离散度倒序排序（离散度最多两个列）
func (im IndexMe) cardinalityReverseSort(IIData []nameColumnMe, Sum int64) (result []string) {
	var cardinality = make(map[string]int64)
	for _, v := range IIData {
		if Sum > 0 && v.Cardinality > 0 && v.Cardinality*100/Sum > 30 {
			cardinality[v.IndexName] = v.Cardinality
		}
	}
	result = ref.MapValueReverseSort(cardinality)
	return
}
func specifyIndexNameToNameColumnMe(specifyIndex string, IIData []nameColumnMe) (p nameColumnMe) {
	for _, vv := range IIData {
		if strings.EqualFold(specifyIndex, vv.IndexName) {
			p = vv
			return
		}
	}
	return
}
func compoundIndexColumnHistogram(IndexName string, cDistinctMeta any) (hist []int64, sum int64) {
	var err error
	for _, v := range cDistinctMeta.([]InnodbIndexStats) {
		if strings.EqualFold(v.IndexName, IndexName) {
			for k, vv := range v.SubStats {
				sum, _ = strconv.ParseInt(strings.TrimSpace(fmt.Sprintf("%v", vv.Rows)), 10, 64)
				if k > 1 || strings.Contains(vv.StatDescription, "DB_ROW_ID") {
					continue
				}
				var hv int64
				if hv, err = strconv.ParseInt(strings.TrimSpace(fmt.Sprintf("%v", vv.StatValue)), 10, 64); err != nil {
					hist = append(hist, 0)
				} else {
					hist = append(hist, hv)
				}
			}
		}
	}
	return
}
func (im IndexMe) columnCardinalityStandard(hist []int64, Sum int64) bool {
	for _, vv := range hist {
		if vv*100/Sum > 30 {
			return true
		}
	}
	return false
}

// 比较单列和两个列的直方图，选择合适的
func (im IndexMe) columnHistogram(parameter parDef.Parameter, IIData []nameColumnMe) (result []string, err error) {
	var columnDistinctMeta global.Return
	if columnDistinctMeta, err = im.ColumnDistinct(parameter); err != nil || columnDistinctMeta.Result == nil {
		return
	}
	var cost = make(map[string]any)
	for _, v := range IIData {
		hist, Sum := compoundIndexColumnHistogram(v.IndexName, columnDistinctMeta.InsideResult)
		switch {
		case len(hist) == 1 && hist[0] > 0 && im.columnCardinalityStandard(hist, Sum):
			cost[v.IndexName] = math.Round((float64(Sum)/float64(hist[0]))*100) / 100
		case len(hist) > 1 && hist[0] > 0 && hist[1] > 0 && im.columnCardinalityStandard(hist, Sum):
			var re, factor float64 = 0, 1
			for _, h := range hist {
				re = factor * math.Round((float64(Sum)/float64(h))*100) / 100
				factor = re
			}
			cost[v.IndexName] = re
		}
	}
	result = ref.MapValueSort(cost)
	return
}
func typeSelection(c IndexColumnMe) (cost int) {
	for _, v := range []map[string]string{intType, timeType, stringType} {
		if t, ok := v[strings.ToUpper(strings.Split(c.ColumnType, "(")[0])]; ok {
			return typeCost[t]
		}
	}
	return 100
}
func specifyIndexColumnTypeCost(IIData []IndexColumnMe) (cost int) {
	for _, v := range IIData {
		cost += typeSelection(v)
	}
	return
}
func (im IndexMe) typeScreening(screeningIndex []string, IIData []nameColumnMe) (result string) {
	var indexTypeCost = make(map[string]any)
	for _, v := range screeningIndex {
		p := specifyIndexNameToNameColumnMe(v, IIData)
		indexTypeCost[v] = specifyIndexColumnTypeCost(p.Column)
	}
	res := ref.MapValueSort(indexTypeCost)
	if len(res) >= 1 {
		result = res[0]
	}
	return
}
func (im IndexMe) KeyChoose(parameter parDef.Parameter) (result global.Return, err error) {
	var res, indexColumnQueryData global.Return
	c := SumF{}
	if res, err = c.Rows(parameter); err != nil || res.Result == nil {
		return
	}
	Sum := res.Result.(int64)
	if indexColumnQueryData, err = im.NameColumn(parameter); err != nil || indexColumnQueryData.Result == nil {
		return
	}
	var IIData = indexColumnQueryData.InsideResult.([]nameColumnMe)
	var screeningIndex []string
	if screeningIndex, err = im.columnHistogram(parameter, IIData); err != nil {
		return
	}
	if len(screeningIndex) == 0 {
		screeningIndex = im.cardinalityReverseSort(IIData, Sum)
	}
	chooseIndex := im.typeScreening(screeningIndex, IIData)
	for _, v := range IIData {
		if strings.EqualFold(v.IndexName, chooseIndex) {
			if result.Result, err = ref.StructToMap(v); err != nil {
				return
			}
		}
	}
	return
}
func (im IndexMe) NameToColumnClip(parameter parDef.Parameter) (result map[string][]string, err error) {
	result = make(map[string][]string)
	if parameter.Meta == nil {
		return
	}
	for _, v := range parameter.Meta.([]map[string]any) {
		var indexCol nameColumnMe
		if err = ref.MapToStruct(v, &indexCol); err != nil {
			return
		}
		result[strings.ToUpper(fmt.Sprintf("%v@%v", indexCol.IndexName, indexCol.ColumnKey))] = func() (res []string) {
			for _, p := range indexCol.Column {
				var col = strings.ToUpper(p.ColumnName)
				if p.SubPart > 0 {
					col = fmt.Sprintf("%v(%v)", strings.ToUpper(p.ColumnName), p.SubPart)
				}
				res = append(res, col)
			}
			return
		}()
	}
	return
}
func (pm PartitionMe) NameToRuleClip(parameter parDef.Parameter) (result global.Return, err error) {
	return
}
